﻿
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports AppsUtility.EncDec
Imports System.Web.Mail
Imports System.Text
Imports Microsoft.Office.Core
Imports System.Configuration


Public Class DmlDdl

    Dim util As New AppsUtility.EncDec
    Public strCon As String = util.toDecrypt(System.Configuration.ConfigurationSettings.AppSettings("SQLString"))

    Public Sub InsertUpdateSalesman(ByVal SalesmanId As Integer, ByVal Salesman As String, ByVal SalesmanCode As String, ByVal SalesmanAdr1 As String, ByVal SalesmanAdr2 As String, ByVal Address As String, ByVal Email As String, ByVal Phone As String, ByVal Mobile As String, ByVal Active As Integer, ByVal CompanyId As Integer)

        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        SqlCmd = New SqlCommand("InsertUpdateSalesman", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@SalesmanId", SalesmanId)
        SqlCmd.Parameters.AddWithValue("@Salesman", Trim(UCase(Salesman)))
        SqlCmd.Parameters.AddWithValue("@SalesmanCode", Trim(UCase(SalesmanCode)))
        SqlCmd.Parameters.AddWithValue("@SalesmanAdr1", SalesmanAdr1)
        SqlCmd.Parameters.AddWithValue("@SalesmanAdr2", SalesmanAdr2)
        SqlCmd.Parameters.AddWithValue("@Address", Address)
        SqlCmd.Parameters.AddWithValue("@Email", Email)
        SqlCmd.Parameters.AddWithValue("@Phone", Phone)
        SqlCmd.Parameters.AddWithValue("@Mobile", Mobile)
        SqlCmd.Parameters.AddWithValue("@Active", Active)
        SqlCmd.Parameters.AddWithValue("@CompanyId", CompanyID)

        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Function LoadSalesman() As SqlDataReader

        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim reader As SqlDataReader

        SqlCmd = New SqlCommand("LoadSalesman", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        Try
            sqlCon.Open()
            reader = SqlCmd.ExecuteReader
            Return reader
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
    End Function

    Public Function LoadSalesmanDetails(ByVal SearchString As String) As SqlDataReader
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim reader As SqlDataReader

        SqlCmd = New SqlCommand("LoadSalesmanDetails", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@SearchString", Trim(UCase(SearchString)))
        Try
            sqlCon.Open()
            reader = SqlCmd.ExecuteReader
            Return reader
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
    End Function

    Public Function CheckIfExist(ByVal SQLQuery As String, ByVal SearchString As String, ByVal Status As Integer) As Integer
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim SqlParm As SqlParameter

        SqlCmd = New SqlCommand(SQLQuery, sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@SearchString", Trim(UCase(SearchString)))
        SqlCmd.Parameters.AddWithValue("@Status", Status)
        SqlParm = SqlCmd.Parameters.AddWithValue("@Id", SqlDbType.Money)
        SqlParm.Direction = ParameterDirection.Output
        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            If Not IsDBNull(SqlCmd.Parameters("@Id").Value) Then
                CheckIfExist = (SqlCmd.Parameters("@Id").Value)
            Else
                CheckIfExist = 0
            End If
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function


    Public Sub KeyDownEvent(ByVal e As System.Windows.Forms.KeyEventArgs, ByVal Text As TextBox, ByVal List As ListBox)
        Dim index As Integer
        index = List.SelectedIndex
        If e.KeyCode = Keys.Down Then
            If index < List.Items.Count - 1 Then
                List.SetSelected(index + 1, True)
            End If
        ElseIf e.KeyCode = Keys.Up Then
            If index > 0 Then
                List.SetSelected(index - 1, True)
            End If
        ElseIf e.KeyCode = Keys.Enter Then
            Text.Text = List.SelectedItem
        End If
    End Sub

    Public Function TextChangeEvent(ByVal Text As TextBox, ByVal List As ListBox) As Boolean
        Dim exist As Boolean
        Dim count As Integer
        For count = 0 To List.Items.Count - 1
            If UCase(Text.Text) = UCase(Mid(List.Items.Item(count), 1, Len(Text.Text))) Then
                List.SetSelected(count, True)
                exist = True
                Exit For
            End If
        Next
        If exist = False Then
            TextChangeEvent = False
        Else
            TextChangeEvent = True
        End If
    End Function

    Public Sub DMLGovernarate(ByVal Name As String, ByVal Id As Integer)

        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        SqlCmd = New SqlCommand("DMLGovernarate", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@Name", Trim(UCase(Name)))
        SqlCmd.Parameters.AddWithValue("@Id", Id)
        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Function GetMasterId(ByVal SqlQuery As String, ByVal SearchString As String) As Integer
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim SqlParm As SqlParameter

        SqlCmd = New SqlCommand(SqlQuery, sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@SearchString", Trim(UCase(SearchString)))
        SqlParm = SqlCmd.Parameters.AddWithValue("@Id", SqlDbType.Money)
        SqlParm.Direction = ParameterDirection.Output
        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            If Not IsDBNull(SqlCmd.Parameters("@Id").Value) Then
                GetMasterId = SqlCmd.Parameters("@Id").Value
            Else
                GetMasterId = 0
            End If
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function
    Public Function LoadName(ByVal SqlQuery As String) As SqlDataReader
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim reader As SqlDataReader

        SqlCmd = New SqlCommand(SqlQuery, sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        Try
            sqlCon.Open()
            reader = SqlCmd.ExecuteReader
            Return reader
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
    End Function

    Public Function LoadAllEmployee(ByVal Company As Integer) As SqlDataReader
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim reader As SqlDataReader

        SqlCmd = New SqlCommand("LoadAllEmployee", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@Company", Company)

        Try
            sqlCon.Open()
            reader = SqlCmd.ExecuteReader
            Return reader
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
    End Function

    Public Sub DMLArea(ByVal Area As String, ByVal Gover As String, ByVal Id As Integer)
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        SqlCmd = New SqlCommand("DMLArea", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@Area", Trim(UCase(Area)))
        SqlCmd.Parameters.AddWithValue("@Gover", Trim(UCase(Gover)))
        SqlCmd.Parameters.AddWithValue("@Id", Id)
        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub DMLBank(ByVal SqlQuery As String, ByVal Name As String, ByVal Id As Integer)
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        SqlCmd = New SqlCommand(SqlQuery, sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@Name", Trim(UCase(Name)))
        SqlCmd.Parameters.AddWithValue("@Id", Id)
        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub DMLNationality(ByVal SqlQuery As String, ByVal Name As String, ByVal Id As Integer)
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        SqlCmd = New SqlCommand(SqlQuery, sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@Name", Trim(UCase(Name)))
        SqlCmd.Parameters.AddWithValue("@Id", Id)
        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub DMLDesignation(ByVal Name As String, ByVal Id As Integer)
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        SqlCmd = New SqlCommand("DMLDesignation", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@Name", Trim(UCase(Name)))
        SqlCmd.Parameters.AddWithValue("@Id", Id)
        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub DMLEmployeeQualification(ByVal Name As String, ByVal Id As Integer)
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        SqlCmd = New SqlCommand("DMLEmployeeQualification", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@Name", Trim(UCase(Name)))
        SqlCmd.Parameters.AddWithValue("@Id", Id)
        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Public Sub DMLEmployeeType(ByVal Name As String, ByVal Id As Integer)
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        SqlCmd = New SqlCommand("DMLEmployeeType", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@Name", Trim(UCase(Name)))
        SqlCmd.Parameters.AddWithValue("@Id", Id)
        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Function LoadEmployeeDetails(ByVal SqlQuery As String, ByVal CompanyId As Integer, ByVal SearchString As String) As SqlDataReader
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim reader As SqlDataReader

        SqlCmd = New SqlCommand(SqlQuery, sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.AddWithValue("@CompanyId", CompanyId)
        SqlCmd.Parameters.AddWithValue("@SearchString", Trim(UCase(SearchString)))

        Try
            sqlCon.Open()
            reader = SqlCmd.ExecuteReader
            Return reader
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
    End Function

    Public Sub InsertUpdateEmployee(ByVal EmpId As Integer, ByVal EmpName As String, ByVal EmpCode As String, ByVal EmpNo As String, ByVal EmpType As String, ByVal EmpBank As String, ByVal AccNo As Integer, ByVal Desig As String, ByVal Nation As String, ByVal Qual As String, ByVal Mobile As Integer, ByVal CivilId As String, ByVal Area As String, ByVal Email As String, ByVal AppoDate As Date, ByVal DOB As Date, ByVal Remarks As String, ByVal IsActive As Integer, ByVal Company As Integer)
        Dim SqlCmd As SqlCommand
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        SqlCmd = New SqlCommand("InsertUpdateEmployee", sqlCon)
        SqlCmd.CommandType = CommandType.StoredProcedure

        SqlCmd.Parameters.AddWithValue("@EmpId", EmpId)
        SqlCmd.Parameters.AddWithValue("@EmpName", Trim(UCase(EmpName)))
        SqlCmd.Parameters.AddWithValue("@EmpCode", Trim(UCase(EmpCode)))
        SqlCmd.Parameters.AddWithValue("@EmpNo", Trim(UCase(EmpNo)))

        SqlCmd.Parameters.AddWithValue("@EmpType", EmpType.Trim)
        SqlCmd.Parameters.AddWithValue("@EmpBank", EmpBank.Trim)
        SqlCmd.Parameters.AddWithValue("@AccNo", AccNo)
        SqlCmd.Parameters.AddWithValue("@Desig", Desig.Trim)
        SqlCmd.Parameters.AddWithValue("@Nation", Nation.Trim)
        SqlCmd.Parameters.AddWithValue("@Qual", Qual.Trim)
        SqlCmd.Parameters.AddWithValue("@Mobile", Mobile)
        SqlCmd.Parameters.AddWithValue("@CivilId", CivilId)
        SqlCmd.Parameters.AddWithValue("@Area", Area.Trim)

        SqlCmd.Parameters.AddWithValue("@Email", Email.Trim)
        SqlCmd.Parameters.AddWithValue("@AppoDate", AppoDate)
        SqlCmd.Parameters.AddWithValue("@DOB", DOB)
        SqlCmd.Parameters.AddWithValue("@Remarks", Remarks.Trim)
        SqlCmd.Parameters.AddWithValue("@IsActive", IsActive)
        SqlCmd.Parameters.AddWithValue("@CompanyId", Company)

        Try
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

End Class
